{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "ein.tags": "worksheet-0",
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "# 使用ORM进行数据库操作\n",
    "\n",
    "Web 程序最常用基于 **关系模型** 的数据库，这种数据库也称为 **SQL** 数据库， 因为它们使用结构化查询语言。\n",
    "\n",
    "**文档数据库** 和 **键值对数据库** ，这两种数据库合称 **NoSQL** 数据库。\n",
    "\n",
    "\n",
    "## Python数据库框架\n",
    "\n",
    "大多数的数据库引擎都有对应的 Python 包，包括开源包和商业包。还有一些数据库抽象层代码包供选择，比如SQLAlchemy，Peewee和MongoEngine等， 可以使用这些抽象包直接处理高等级的 Python 对象，而不用处理如表、文档或查询语言 此类的数据库实体。\n",
    "\n",
    "选择数据库框架时，要考虑的因素：\n",
    "\n",
    "-   易用性\n",
    "\n",
    "    如果直接比较数据库引擎和数据库抽象层，显然后者取胜。抽象层，也称为对象关系映射（Object-Relational Mapper，ORM）或 对象文档映射（Object-Document Mapper，ODM），在用户不知觉的情况下把高层的 面向对象操作转换成低层的数据库指令。\n",
    "\n",
    "-   性能\n",
    "\n",
    "    ORM 和 ODM 把对象业务转换成数据库业务会有一定的损耗。大多数情况下，这种性能的降低微不足道，但也不一定都是如此。一般情况下，ORM 和 ODM 对生产率的提升远远超过了这一丁点儿的性能降低， 所以性能降低这个理由不足以说服用户完全放弃 ORM 和 ODM。 **真正的关键点在于如何选择一个能直接操作低层数据库的抽象层，以防特定的操作需要直接使用数据库原生指令优化。**\n",
    "\n",
    "-   可移植性\n",
    "\n",
    "    选择数据库时，必须考虑其是否能在你的开发平台和生产平台中使用。例如，如果你打 算利用云平台托管程序，就要知道这个云服务提供了哪些数据库可供选择。\n",
    "\n",
    "    可移植性还针对 ORM 和 ODM。尽管有些框架只为一种数据库引擎提供抽象层，但其 他框架可能做了更高层的抽象，它们支持不同的数据库引擎， 而且都使用相同的面向对象接口。\n",
    "\n",
    "-   Flask 集成度\n",
    "\n",
    "    选择框架时，不一定非得选择已经集成了 Flask 的框架，但选择这些框架可以节省 编写集成代码的时间。使用集成了 Flask 的框架可以简化配置和操作。\n",
    "\n",
    "基于以上因素，我们最终选择 [Peewee](http://docs.peewee-orm.com/) 。\n",
    "\n",
    "\n",
    "## 在Flask中使用Peewee\n",
    "\n",
    "\n",
    "### 定义模型\n",
    "\n",
    "在`hello.py`中定义Role和User模型：\n",
    "\n",
    "```python\n",
    "import peewee as pw\n",
    "\n",
    "\n",
    "db = pw.SqliteDatabase(\"flaskr.db\")\n",
    "\n",
    "\n",
    "class BaseModel(pw.Model):\n",
    "    class Meta:\n",
    "        database = db\n",
    "\n",
    "\n",
    "class Role(BaseModel):\n",
    "    name = pw.CharField(64, unique=True)\n",
    "\n",
    "    def __repr__(self):\n",
    "        return '<Role %r>' % self.name\n",
    "\n",
    "    class Meta:\n",
    "        db_table = 'roles'\n",
    "\n",
    "\n",
    "class User(BaseModel):\n",
    "    username = pw.CharField(64, unique=True, index=True)\n",
    "\n",
    "    def __repr__(self):\n",
    "        return '<User %r>' % self.username\n",
    "\n",
    "    class Meta:\n",
    "        db_table = 'users'\n",
    "```\n",
    "\n",
    "下表列出了一些可用的字段类型以及对应的数据库字段类型：\n",
    "\n",
    "| 字段类型            | Sqlite   | Postgresql       | MySQL            |\n",
    "|---------------------|----------|------------------|------------------|\n",
    "| `CharField`         | varchar  | varchar          | varchar          |\n",
    "| `FixedCharField`    | char     | char             | char             |\n",
    "| `TextField`         | text     | text             | longtext         |\n",
    "| `DateTimeField`     | datetime | timestamp        | datetime         |\n",
    "| `IntegerField`      | integer  | integer          | integer          |\n",
    "| `BooleanField`      | integer  | boolean          | bool             |\n",
    "| `FloatField`        | real     | real             | real             |\n",
    "| `DoubleField`       | real     | double precision | double precision |\n",
    "| `BigIntegerField`   | integer  | bigint           | bigint           |\n",
    "| `SmallIntegerField` | integer  | smallint         | smallint         |\n",
    "| `DecimalField`      | decimal  | numeric          | numeric          |\n",
    "| `PrimaryKeyField`   | integer  | serial           | integer          |\n",
    "| `ForeignKeyField`   | integer  | integer          | integer          |\n",
    "| `DateField`         | date     | date             | date             |\n",
    "| `TimeField`         | time     | time             | time             |\n",
    "| `TimestampField`    | integer  | integer          | integer          |\n",
    "| `BlobField`         | blob     | bytea            | blob             |\n",
    "| `UUIDField`         | text     | uuid             | varchar(40)      |\n",
    "| `BareField`         | untyped  | not supported    | not supported    |\n",
    "\n",
    "字段初始化参数及默认值：\n",
    "\n",
    "-   `null = False` – 布尔值，是否允许储存`null`值。\n",
    "-   `index = False` – 布尔值，是否为这一列添加索引。\n",
    "-   `unique = False` – 布尔值，是否为这一列添加唯一性索引。另外可参考如何添加复合索引。\n",
    "-   `verbose_name = None` – 字符串，为模型字段添加用户友好的自定义标注。\n",
    "-   `help_text = None` – 字符串，为此字段添加帮助文本信息。\n",
    "-   `db_column = None` – 字符串，用于底层存储的列名，有助于遗留数据库的兼容性。\n",
    "-   `default = None` – 任意类型，用于初始化的默认值，如果是可调用对象，则调用生成相应的值。\n",
    "-   `choices = None` – 可迭代的二元元组，对应`value`和`display`。\n",
    "-   `primary_key = False` – 布尔值，是否时此数据表的主键。\n",
    "-   `sequence = None` – 字符串，序列名字，如果后端数据库支持的话。\n",
    "-   `constraints = None` - 一个或多个约束条件列表，例如`[Check('price > 0')]`。\n",
    "-   `schema = None` – 字符串，schema的可选名字，如果后端数据库支持的话。\n",
    "\n",
    "一些列类型接收特定的参数：\n",
    "\n",
    "| 字段类型          | 特殊参数                                                                   |\n",
    "|-------------------|----------------------------------------------------------------------------|\n",
    "| `CharField`       | `max_length`                                                               |\n",
    "| `FixedCharField`  | `max_length`                                                               |\n",
    "| `DateTimeField`   | `formats`                                                                  |\n",
    "| `DateField`       | `formats`                                                                  |\n",
    "| `TimeField`       | `formats`                                                                  |\n",
    "| `TimestampField`  | `resolution`, `utc`                                                        |\n",
    "| `DecimalField`    | `max_digits`, `decimal_places`, `auto_round`, `rounding`                   |\n",
    "| `ForeignKeyField` | `rel_model`, `related_name`, `to_field`, `on_delete`, `on_update`, `extra` |\n",
    "| `BareField`       | `coerce`                                                                   |\n",
    "\n",
    "虽然没有强制要求，但这两个模型都定义了`__repr()__`方法，返回一个具有可读性的字符 串表示模型，可在调试和测试时使用。\n",
    "\n",
    "\n",
    "### 关系\n",
    "\n",
    "关系型数据库使用关系把不同表中的行联系起来。\n",
    "\n",
    "角色到用户是 **一对多** 关系，因为一个角色可属于多个用户，而每个用户都只能有一个角色。\n",
    "\n",
    "```python\n",
    "class User(BaseModel):\n",
    "    # ...\n",
    "    role = pw.ForeignKeyField(Role, related_name='users', null=True)\n",
    "```\n",
    "\n",
    "\n",
    "### 数据库操作\n",
    "\n",
    "学习如何使用模型的最好方法是在 Python shell 中实际操作。\n",
    "\n",
    "\n",
    "#### 创建表\n",
    "\n",
    "首先，要根据模型类来创建数据库。\n",
    "\n",
    "定义一个函数`create_tables`，用来创建数据表。\n",
    "\n",
    "```python\n",
    "def create_tables():\n",
    "    db.connect()\n",
    "    db.create_tables([Role, User])\n",
    "```\n",
    "\n",
    "接下来在开启Flask shell 并进行实际操作：\n",
    "\n",
    "```sh\n",
    "(flaskr_env3) $ export FLASK_APP=hello.py\n",
    "(flaskr_env3) $ export FLASK_DEBUG=1\n",
    "(flaskr_env3) $ flask shell\n",
    "```\n",
    "\n",
    "    ...\n",
    "    App: hello [debug]\n",
    "    Instance: ...\n",
    "\n",
    "```python\n",
    ">>> from hello import create_tables\n",
    ">>> create_tables()\n",
    "```\n",
    "\n",
    "\n",
    "#### 插入行\n",
    "\n",
    "```python\n",
    ">>> from hello import Role, User\n",
    ">>> admin_role = Role(name='Admin')\n",
    ">>> mod_role = Role(name='Moderator')\n",
    ">>> user_role = Role(name='User')\n",
    ">>> user_john = User(username='john', role=admin_role)\n",
    ">>> user_susan = User(username='susan', role=user_role)\n",
    ">>> user_david = User(username='david', role=user_role)\n",
    ">>> print(admin_role.id)\n",
    "None\n",
    ">>> admin_role.save()\n",
    ">>> mod_role.save()\n",
    ">>> user_role.save()\n",
    ">>> user_john.save()\n",
    ">>> user_susan.save()\n",
    ">>> user_david.save()\n",
    "```\n",
    "\n",
    "再查看`id`属性，现在已经赋值了：\n",
    "\n",
    "```python\n",
    ">>> print(admin_role.id)\n",
    "1\n",
    ">>> print(mod_role.id)\n",
    "2\n",
    ">>> print(user_role.id)\n",
    "3\n",
    "```\n",
    "\n",
    "\n",
    "#### 修改行\n",
    "\n",
    "```python\n",
    ">>> admin_role.name = 'Administrator'\n",
    ">>> admin_role.save()\n",
    "```\n",
    "\n",
    "\n",
    "#### 删除行\n",
    "\n",
    "```python\n",
    ">>> mod_role.delete_instance()\n",
    "1\n",
    "```\n",
    "\n",
    "\n",
    "#### 查询行\n",
    "\n",
    "```python\n",
    ">>> list(Role.select())\n",
    "[<Role 'Administrator'>, <Role 'User'>]\n",
    ">>> list(User.select())\n",
    "[<User 'john'>, <User 'susan'>, <User 'david'>]\n",
    "```\n",
    "\n",
    "查找角色为 \"User\" 的所有用户：\n",
    "\n",
    "```python\n",
    ">>> list(User.select().where(User.role==user_role))\n",
    "[<User 'susan'>, <User 'david'>]\n",
    "```\n",
    "\n",
    "查看Peewee为查询生成的原生SQL查询语句：\n",
    "\n",
    "```python\n",
    ">>> print(User.select().where(User.role==user_role).sql())\n",
    "('SELECT \"t1\".\"id\", \"t1\".\"username\", \"t1\".\"role_id\" FROM \"users\" AS t1 WHERE (\"t1\".\"role_id\" = ?)', [3])\n",
    "```\n",
    "\n",
    "如果你退出了 shell 会话，前面这些例子中创建的对象就不会以 Python 对象的形式存在， 而是作为各自数据库表中的行。如果你打开了一个新的 shell 会话，就要从数据库中读取行， 再重新创建 Python 对象。\n",
    "\n",
    "```python\n",
    ">>> user_role = Role.select().where(Role.name=='User').get()\n",
    "```\n",
    "\n",
    "关系和查询的处理方式类似。下面这个例子分别从关系的两端查询角色和用户之间的一对多关系：\n",
    "\n",
    "```python\n",
    ">>> users = user_role.users\n",
    ">>> list(users)\n",
    "[<User 'susan'>, <User 'david'>]\n",
    ">>> users[0].role\n",
    "<Role 'User'>\n",
    "```\n",
    "\n",
    "**🔖 执行`git checkout 4a`签出程序的这个版本。**\n",
    "\n",
    "\n",
    "### 在视图函数中操作数据库\n",
    "\n",
    "前一节介绍的数据库操作可以直接在视图函数中进行。\n",
    "\n",
    "下面的代码示例展示了首页路由的新版本，把用户输入的名字写入数据库。\n",
    "\n",
    "```python\n",
    "@app.route('/', methods=['GET', 'POST'])\n",
    "def index():\n",
    "    form = NameForm()\n",
    "    if form.validate_on_submit():\n",
    "        user = User.select().where(User.username == form.name.data).first()\n",
    "        if user is None:\n",
    "            user = User(username=form.name.data)\n",
    "            user.save()\n",
    "            session['known'] = False\n",
    "        else:\n",
    "            session['known'] = True\n",
    "        session['name'] = form.name.data\n",
    "        form.name.data = ''\n",
    "        return redirect(url_for('index'))\n",
    "    return render_template('index.html', form=form,\n",
    "                           name=session.get('name'),\n",
    "                           known=session.get('known', False))\n",
    "```\n",
    "\n",
    "变量`known`被写入用户会话中，因此重定向之后，可以把数据传给模板， 用来显示自定义的欢迎消息。\n",
    "\n",
    "对应的模板新版本如下所示，这个模板使用`known`参数在欢迎消息中加入了第二行， 从而对已知用户和新用户显示不同的内容。\n",
    "\n",
    "```django\n",
    "{# templates/index.html #}\n",
    "\n",
    "{% extends \"base.html\" %}\n",
    "{% import \"bootstrap/wtf.html\" as wtf %}\n",
    "\n",
    "{% block page_content %}\n",
    "    <div class=\"page-header\">\n",
    "        <h1>Hello, {% if name %}{{ name }}{% else %}Stranger{% endif %}!</h1>\n",
    "        {% if not known %}\n",
    "            <p>Pleased to meet you!</p>\n",
    "        {% else %}\n",
    "            <p>Happy to see you again!</p>\n",
    "        {% endif %}\n",
    "    </div>\n",
    "    {{ wtf.quick_form(form) }}\n",
    "{% endblock %}\n",
    "```\n",
    "\n",
    "**🔖 执行`git checkout 4b`签出程序的这个版本。**\n",
    "\n",
    "\n",
    "## 使用Flask-PW\n",
    "\n",
    "为了方便在项目中集成Peewee，后面的章节里我们将使用一个扩展 [Flask-PW](https://github.com/klen/flask-pw)，其提供了数据库连接配置及一些工具如数据库 迁移和信号，另外，它还为在[Flask-Debugtoolbar](https://flask-debugtoolbar.readthedocs.org/en/latest/) 使用Peewee提供了支持。\n",
    "\n",
    "\n",
    "## 数据库迁移\n",
    "\n",
    "目前，Peewee尚未支持自动数据库迁移，但是可以使用其提供的`playhouse.migrate`模块来创建 简单的迁移脚本。\n",
    "\n",
    "> Peewee’s migrations do not handle introspection and database “versioning”. Rather, peewee provides a number of helper functions for generating and running schema-altering statements. This engine provides the basis on which a more sophisticated tool could some day be built.\n",
    ">\n",
    "> Migrations can be written as simple python scripts and executed from the command-line. Since the migrations only depend on your applications Database object, it should be easy to manage changing your model definitions and maintaining a set of migration scripts without introducing dependencies.\n",
    "\n",
    "下面是一个例子：\n",
    "\n",
    "```python\n",
    "from playhouse.migrate import SqliteMigrator, migrate\n",
    "import peewee as pw\n",
    "\n",
    "\n",
    "my_db = pw.SqliteDatabase('my_database.db')\n",
    "migrator = SqliteMigrator(my_db)\n",
    "\n",
    "title_field = pw.CharField(default='')\n",
    "status_field = pw.IntegerField(null=True)\n",
    "\n",
    "# run migrations inside a transaction\n",
    "with my_db.transaction():\n",
    "    migrate(\n",
    "        migrator.add_column('some_table', 'title', title_field),\n",
    "        migrator.add_column('some_table', 'status', status_field),\n",
    "        migrator.drop_column('some_table', 'old_column'),\n",
    "    )\n",
    "```\n",
    "\n",
    "**支持的操作：**\n",
    "\n",
    "-   为已有模型添加新字段\n",
    "\n",
    "    ```python\n",
    "    # Create your field instances. For non-null fields you must specify a\n",
    "    # default value.\n",
    "    pubdate_field = DateTimeField(null=True)\n",
    "    comment_field = TextField(default='')\n",
    "\n",
    "    # Run the migration, specifying the database table, field name and field.\n",
    "    migrate(\n",
    "        migrator.add_column('comment_tbl', 'pub_date', pubdate_field),\n",
    "        migrator.add_column('comment_tbl', 'comment', comment_field),\n",
    "    )\n",
    "    ```\n",
    "\n",
    "-   重命名字段\n",
    "\n",
    "    ```python\n",
    "    # Specify the table, original name of the column, and its new name.\n",
    "    migrate(\n",
    "        migrator.rename_column('story', 'pub_date', 'publish_date'),\n",
    "        migrator.rename_column('story', 'mod_date', 'modified_date'),\n",
    "    )\n",
    "    ```\n",
    "\n",
    "-   删除字段\n",
    "\n",
    "    ```python\n",
    "    migrate(\n",
    "        migrator.drop_column('story', 'some_old_field'),\n",
    "    )\n",
    "    ```\n",
    "\n",
    "-   设置字段 nullable 或者 not nullable\n",
    "\n",
    "    ```python\n",
    "    # Note that when making a field not null that field must not have any\n",
    "    # NULL values present.\n",
    "    migrate(\n",
    "        # Make `pub_date` allow NULL values.\n",
    "        migrator.drop_not_null('story', 'pub_date'),\n",
    "\n",
    "        # Prevent `modified_date` from containing NULL values.\n",
    "        migrator.add_not_null('story', 'modified_date'),\n",
    "    )\n",
    "    ```\n",
    "\n",
    "-   添加索引\n",
    "\n",
    "    ```python\n",
    "    # Specify the table, column names, and whether the index should be\n",
    "    # UNIQUE or not.\n",
    "    migrate(\n",
    "        # Create an index on the `pub_date` column.\n",
    "        migrator.add_index('story', ('pub_date',), False),\n",
    "\n",
    "        # Create a multi-column index on the `pub_date` and `status` fields.\n",
    "        migrator.add_index('story', ('pub_date', 'status'), False),\n",
    "\n",
    "        # Create a unique index on the category and title fields.\n",
    "        migrator.add_index('story', ('category_id', 'title'), True),\n",
    "    )\n",
    "    ```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  },
  "name": "5-peewee-orm.ipynb",
  "toc": {
   "colors": {
    "hover_highlight": "#ddd",
    "running_highlight": "#FF0000",
    "selected_highlight": "#ccc"
   },
   "moveMenuLeft": true,
   "nav_menu": {
    "height": "264px",
    "width": "252px"
   },
   "navigate_menu": true,
   "number_sections": false,
   "sideBar": true,
   "threshold": 4,
   "toc_cell": false,
   "toc_section_display": "block",
   "toc_window_display": false,
   "widenNotebook": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
